use master
if exists (select * from sysdatabases where name='Wonder_Lower_Price') 
begin
alter database Wonder_Lower_Price
set offline with rollback immediate
alter database Wonder_Lower_Price
set online
drop database Wonder_Lower_Price
end

create database Wonder_Lower_Price
go

use Wonder_Lower_Price
go



---------create table-----------------------------
	-- create table Manager
	create table Manager
	(
		ManagerID varchar(10) not null primary key,
		ManagerAccount varchar(50) not null,
		ManagerPassword varchar(32) not null, 
	)
	go

	--create table Timework
	create table Timework
	(
		TimeworkID varchar(10) not null primary key,
		Description varchar(100) not null,
		IsDeleted tinyint not null default(0)
	)
	go

	--create table Permission
	create table Permission
	(
		PermissionID varchar(10) not null primary key,
		Description varchar(100) not null,
		IsDeleted tinyint not null default(0)
	)
	go


	--create table Customer
	create table Customer
	(
		CustomerID varchar(10) not null primary key,
		CustomerName varchar(50) not null,
		CustomerGender varchar(6) not null,
		CustomerBirthDay date not null,
		CustomerAddress varchar(100) not null,
		CustomerPhone varchar(15) not null,
		CustomerEmail varchar(50),
		IsDeleted tinyint not null default(0)
	)
	go

	--create table Driver
	create table Driver
	(
		DriverID varchar(10) not null primary key,
		DriverName varchar(50) not null,
		DriverGender varchar(6) not null,
		DriverBirthDay date not null,
		DriverPhone varchar(15) not null,
		DriverAddress varchar(100) not null,
		TimeworkID varchar(10) not null,
		IsDeleted tinyint not null default(0)
	)
	go

	--create table Employee
	create table Employee
	(
		EmployeeID varchar(10) not null primary key,
		EmployeeName varchar(50) not null,
		EmployeeBirthDay date not null,
		EmployeeGender varchar(6) not null,
		EmployeeAddress varchar(100) not null,
		EmployeePhone varchar(15) not null,
		PermissionID varchar(10) not null,
		TimeworkID varchar(10) not null,
		EmployeeAccount varchar(50) not null,
		EmployeePassword varchar(32) not null,
		IsDeleted tinyint not null default(0)
	)
	go
	
	--create table Vendor
	create table Vendor
	(
		VendorID varchar(10) not null primary key,
		VendorName varchar(100) not null,
		VendorPhone varchar(15) not null,
		VendorAddress varchar(100) not null,
		VendorEmail varchar(50) not null,
		IsDeleted tinyint not null default(0)
	)
	go

	--create table Category
	create table Category
	(
		CategoryID varchar(10) not null primary key,
		CategoryName varchar(50) not null,
		IsDeleted tinyint not null default(0)
	)
	go

	

	--create table Product
	create table Product
	(
		ProductID varchar(10) not null primary key,
		ProductName varchar(50) not null,
		ProductUnits varchar(20) not null,
		ProductPrice float not null,
		ProductQuantities int not null,
		CategoryID varchar(10) not null,
		VendorID varchar(10) not null,
		Description varchar(100),
		IsDeleted tinyint not null default(0)
	)
	go

	--create table Holiday
	create table Holiday
	(
		HolidayID varchar(10) not null primary key,
		HolidayName varchar(100) not null,
		HolidayDate varchar(10) not null,
		Discount float not null,
	)
	go

	
	--create table TypePay
	create table TypePay
	(
		TypePayID varchar(10) not null primary key,
		TypePayName varchar(50) not null,
		IsDeleted tinyint not null default(0)
	)
	go

	--create table Sale Orders
	create table SaleOrders
	(
		SaleOrdersID varchar(15) not null primary key,
		DayCreated date not null,
		CustomerID varchar(10) not null,
		EmployeeID varchar(10) not null,
		Discount float,
		StockIssue tinyint not null default(0),
		Shipped tinyint not null default(0),
		HomeDelivery tinyint not null default(0),
		TypePayID varchar(10) not null,
		Note varchar(100),
		Payment tinyint not null,
		Total float not null,
		IsDeleted tinyint not null default(0)
	)
	go

	--create table Sale Orders Detail
	create table SaleOrdersDetail
	(
		SaleOrdersID varchar(15) not null,
		ProductID varchar(10) not null,
		ProductPrice float not null,
		Quantities int not null,
		primary key(SaleOrdersID,ProductID),

	)
	go


	--create table StockIssue
	create table StockIssue
	(
		StockIssueID varchar(15) not null primary key,
		SaleOrdersID varchar(15) not null,
		DayCreated date not null,		
		EmployeeID varchar(10) not null,
		IsDeleted tinyint not null default(0)
	)
	go

	--create table PurchaseOrders
	create table PurchaseOrders
	(
		PurchaseOrdersID varchar(15) not null primary key,
		DayCreated date not null,
		ReceiptDate date,
		EmployeeID varchar(10) not null,
		VendorID varchar(10) not null,
		Status tinyint not null default(0),
		Approve tinyint not null default(0),
		Description varchar(100),
		Total float not null,
		IsDeleted tinyint not null default(0)
	)
	go

	--create table PurchaseOrdersDetail
	create table PurchaseOrdersDetail
	(
		PurchaseOrdersID varchar(15) not null,
		ProductID varchar(10) not null,
		Quantities int not null,
		primary key (PurchaseOrdersID,ProductID)
	)
	go

	--create table StockReceipt
	create table StockReceipt
	(
		StockReceiptID varchar(15) not null primary key,
		DayCreated date not null,
		EmployeeID varchar(10) not null,
		PurchaseOrdersID varchar(15) not null,
		IsDeleted tinyint not null default(0)
	)
	go

	
	--create table Alloction Dispatch
	create table AllocationDispatch
	(
		AllocationDispatchID varchar(10) not null primary key,
		SaleOrdersID varchar(15) not null,
		DriverID varchar(10) not null,
		EmployeeID varchar(10) not null,
		DeliveryStatus varchar(15)not null,
		Note varchar(100),
		IsDeleted tinyint not null default(0)
	)
	go

----------------------------------------------------

--------------add constraint--------------------------

	--table Employee
	alter table Employee
	add constraint FK_PermissionID foreign key (PermissionID)
	references Permission(PermissionID)
	go
	alter table Employee
	add constraint FK_TimeworkID foreign key (TimeworkID)
	references Timework(TimeworkID)
	go
	
	--table Product

	alter table Product
	add constraint FK_CategoryID foreign key (CategoryID)
	references Category(CategoryID)
	go
	alter table Product
	add constraint FK_Vendor9 foreign key (VendorID)
	references Vendor(VendorID)
	go
	--table Sale Orders
	alter table SaleOrders
	add constraint FK_CustomerID foreign key (CustomerID)
	references Customer(CustomerID)
	go
	alter table SaleOrders
	add constraint FK_TypePayID foreign key (TypePayID)
	references TypePay(TypePayID)
	go
	alter table SaleOrders
	add constraint FK_EmployeeID2 foreign key (EmployeeID)
	references Employee(EmployeeID)
	go
	
	--table Stock Issue
	alter table StockIssue
	add constraint FK_SaleOrdersID foreign key (SaleOrdersID)
	references SaleOrders(SaleOrdersID)
	go
	alter table StockIssue
	add constraint FK_EmployeeID3 foreign key (EmployeeID)
	references Employee(EmployeeID)
	go
	
	
	--table Stock Receipt
	alter table StockReceipt
	add constraint FK_PurchaseOrdersID foreign key (PurchaseOrdersID)
	references PurchaseOrders(PurchaseOrdersID)
	go
	alter table StockReceipt
	add constraint FK_EmployeeID4 foreign key (EmployeeID)
	references Employee(EmployeeID)
	go
	
	--table Purchase Orders
	alter table PurchaseOrders
	add constraint FK_VendorID2 foreign key (VendorID)
	references Vendor(VendorID)
	go
	alter table PurchaseOrders
	add constraint FK_EmployeeID5 foreign key (EmployeeID)
	references Employee(EmployeeID)
	go
	
	--table Driver
	alter table Driver
	add constraint FK_TimeworkID5 foreign key (TimeworkID)
	references Timework(TimeworkID)
	go
	
	--table AllocationDispatch
	alter table AllocationDispatch
	add constraint FK_EmployeeID6 foreign key (EmployeeID)
	references Employee(EmployeeID)
	go
	alter table AllocationDispatch
	add constraint FK_SaleOrdersID2 foreign key (SaleOrdersID)
	references SaleOrders(SaleOrdersID)
	go
	alter table AllocationDispatch
	add constraint FK_DriverID foreign key (DriverID)
	references Driver(DriverID)
	go
	
	--table SaleOrdersDetail	
	alter table SaleOrdersDetail
	add constraint FK_SaleOrdersID3 foreign key (SaleOrdersID)
	references SaleOrders(SaleOrdersID)
	go
	alter table SaleOrdersDetail
	add constraint FK_ProductID1 foreign key (ProductID)
	references Product(ProductID)
	go
	
	
	--table PurchaseOrdersDetail
	alter table PurchaseOrdersDetail
	add constraint FK_PurchaseOrdersID2 foreign key (PurchaseOrdersID)
	references PurchaseOrders(PurchaseOrdersID)
	go
	alter table PurchaseOrdersDetail
	add constraint FK_ProductID3 foreign key (ProductID)
	references Product(ProductID)
	go
	
-------------------------------------------------------------

---------- INSERT DATA --------------------------------------

---- insert table Manager  -----
insert into Manager(ManagerID,ManagerAccount,ManagerPassword) values
('M01','manhnk','FCEA920F7412B5DA7BE0CF42B8C93759'),
('M02','hakutu','E10ADC3949BA59ABBE56E057F20F883E'),
('M03','nhatanh','827CCB0EEA8A706C4C34A16891F84E7B'),
('M04','ngoctien','25D55AD283AA400AF464C76D713C07AD')

go

select * from Manager
go

---- insert table Customer  -----
insert into Customer(CustomerID,CustomerName,CustomerGender,CustomerBirthDay,CustomerAddress,CustomerPhone,CustomerEmail) values
('Cus01','Nguyen Thanh Ha','Male','09/10/1992','Phu Tho','01672516272','NguyenHa091092@gmail.com'),
('Cus02','Nguyen Kim Manh','Male','03/03/1992','Ha Noi','0123456678','KimManh@gmail.com'),
('Cus03','Hoang Ngoc Nhat Anh','Male','05/10/1992','Thanh Hoa','01233445566','NhatAnh@gmail.com'),
('Cus04','Nguyen Thi Viet Chinh','Female','10/21/1992','Hai Duong','0123456789','VietChinh@gmail.com')
go
select * from Customer
go

	---- insert table Holiday  -----
insert into Holiday values
('H01','Tet Holiday','15/08',0.05),
('H02','New Year Day','01/01',0.05),
('H03','Mid-Autumn Festival','15/08',0.05),
('H04','National Day','09/02',0.05)
go
select * from Holiday
go

---- insert table Vendor  -----
insert into Vendor(VendorID,VendorName,VendorPhone,VendorAddress,VendorEmail) values
('Ven01','Hai Ha Company','0987654321','12A Hoang Quoc Viet-Ha Noi','haiha.co@gmail.com'),
('Ven02','Minh Ha Company','0989988778','312 Ha Huy Tap-Ha Noi','minhhacompany@gmail.com'),
('Ven03','Kinh Do Hoa Company','0988990011','Gia Lam - Ha Noi','kinhdoco@gmail.com'),
('Ven04','Material MC Company','09875566321','20 Pho Hue - Ha Noi','mc@gmail.com')
go

select * from Vendor
go



---- insert table Type Pay  -----
insert into TypePay(TypePayID,TypePayName) values
('TP01','Cash'),
('TP02','Card Swiping')
go
select * from TypePay
go



---- insert table Timework  -----
insert into Timework(TimeworkID,Description) values 
('TW01','8h00 am - 12h pm'),
('TW02','12h00 pm - 16h pm'),
('TW03','16h00 pm - 20h pm')
go

select * from Timework
go


 ---- insert table Driver ----
insert into Driver(DriverID,DriverName,DriverGender,DriverBirthDay,DriverPhone,DriverAddress,TimeworkID) values
('Dr01','Hoang Tuan Linh','Male','09/10/1982','0987778863','Tu Liem, Ha Noi','TW01'),
('Dr02','Vu Thi Linh','Female','01/15/1987','0988978863','Nam Sach, Hai Duong','TW02'),
('Dr03','Dinh Ngoc Tien','Male','01/01/1988','0988888869','Dong Da, Ha Noi','TW03')
go
select * from Driver
go

 ---- insert table Permission ----
insert into Permission(PermissionID,Description) values
('Per01','Salesperson'),
('Per02','Storekeeper'),
('Per03','Dispatch master')
go
select * from Permission
go

 ---- insert table Employee ----
insert into Employee(EmployeeID,EmployeeName,EmployeeBirthDay,EmployeeGender,EmployeeAddress,EmployeePhone,PermissionID,TimeworkID,EmployeeAccount,EmployeePassword) values
('Em01','Nguyen Thi Huong','06/15/1992','Female','Nam Dinh','0123456789','Per01','TW01','huong1506','FCEA920F7412B5DA7BE0CF42B8C93759'),
('Em02','Vu Truong Long','01/20/1989','Male','Ha Noi','0123556677','Per02','TW01','Long2010','FCEA920F7412B5DA7BE0CF42B8C93759'),
('Em03','Luu thi Lan','01/16/1993','Female','Ha Noi','0122556677','Per03','TW01','LanHN','FCEA920F7412B5DA7BE0CF42B8C93759'),
('Em04','Do Thu Huyen','11/21/1990','Female','Phu Tho','0123552188','Per01','TW02','huyenpt','FCEA920F7412B5DA7BE0CF42B8C93759'),
('Em05','Dinh Ha Ly','01/19/1991','Female','Ha Noi','012346677','Per02','TW02','ly1901','FCEA920F7412B5DA7BE0CF42B8C93759'),
('Em06','Do Van Minh','12/12/1992','Male','Bac Giang','0123568977','Per01','TW03','MinhBG','FCEA920F7412B5DA7BE0CF42B8C93759'),
('Em07','Tran Van Than','12/20/1992','Male','Ha Noi','0122556688','Per02','TW03','ThanHT','FCEA920F7412B5DA7BE0CF42B8C93759')
go
select * from Employee
go

 ---- insert table Category ----
insert into Category(CategoryID,CategoryName) values
('Cate01','Rice'),
('Cate02','Corn'),
('Cate03','Vegetables'),
('Cate04','Oil')
go
select * from Category
go


 ---- insert table Product ----
insert into Product(ProductID,ProductName,ProductUnits,ProductPrice,ProductQuantities,CategoryID,VendorID) values
('Pro01','Parboiled Rice','kilograms',15000,100,'Cate01','Ven01'),
('Pro02','Glutinous Rice','kilograms',13000,100,'Cate01','Ven01'),
('Pro03','White Rice','kilograms',12000,60,'Cate01','Ven02'),
('Pro04','Brown Rice','kilograms',11000,50,'Cate01','Ven03'),
('Pro05','Glutinous corn','kilograms',10000,50,'Cate02','Ven04'),
('Pro06','Sweet corn','kilograms',13000,50,'Cate02','Ven02'),
('Pro07','Spinach','kilograms',5000,60,'Cate03','Ven02'),
('Pro08','Kale','kilograms',7000,50,'Cate03','Ven01'),
('Pro09','Olive oil','jar',20000,50,'Cate04','Ven01'),
('Pro10','Sesame oil','jar',25000,50,'Cate04','Ven03')
go
select * from Product
go


 ---- insert table PurchaseOrders ----
insert into PurchaseOrders(PurchaseOrdersID,DayCreated,EmployeeID,VendorID,Total) values
('Pur01','12/20/2013','Em02','Ven01',150000),
('Pur02','12/21/2013','Em02','Ven02',75000),
('Pur03','12/21/2013','Em07','Ven03',500000),
('Pur04','12/25/2013','Em05','Ven02',84000)
go
select * from PurchaseOrders
go

 ---- insert table Purchase Orders detail ----
insert into PurchaseOrdersDetail values
('Pur01','Pro01',10),
('Pur02','Pro07',15),
('Pur03','Pro09',25),
('Pur04','Pro08',12)
go
select * from PurchaseOrdersDetail
go



 ---- insert table Sale Orders ----
insert into SaleOrders(SaleOrdersID,DayCreated,CustomerID,EmployeeID,TypePayID,HomeDelivery,StockIssue,Shipped,Payment,Total) values
('15-01-2014-1','01/15/2014','Cus01','Em01','TP01',1,1,1,1,150000),
('15-01-2014-2','01/15/2014','Cus02','Em01','TP01',1,1,2,1,75000),
('15-01-2014-3','01/16/2014','Cus03','Em04','TP01',0,0,0,0,500000),
('15-01-2014-4','01/16/2014','Cus04','Em04','TP02',0,1,1,1,84000)
go
select * from SaleOrders
go

--- insert table SaleOrdersDetail
insert into SaleOrdersDetail values
('15-01-2014-1','Pro01',15000,10),
('15-01-2014-2','Pro07',5000,15),
('15-01-2014-3','Pro09',20000,25),
('15-01-2014-4','Pro08',7000,12)
go
select * from SaleOrdersDetail
go




 ---- insert table StockIssue ----
insert into StockIssue(StockIssueID,SaleOrdersID,DayCreated,EmployeeID) values
('SI01','15-01-2014-1','01/15/2014','Em02'),
('SI02','15-01-2014-2','01/15/2014','Em02'),
('SI03','15-01-2014-4','01/16/2014','Em05')
go
select * from StockIssue
go

 ---- insert table StockReceipt ----
insert into StockReceipt(StockReceiptID,DayCreated,EmployeeID,PurchaseOrdersID) values
('SR01','12/20/2013','Em02','Pur01'),
('SR02','12/21/2013','Em07','Pur02'),
('SR03','12/21/2013','Em02','Pur03'),
('SR04','12/25/2013','Em05','Pur04')
go
select * from StockReceipt
go

---- insert table Allocation Dispatch----
insert AllocationDispatch(AllocationDispatchID,SaleOrdersID,DriverID,EmployeeID,DeliveryStatus,Note) values
('AD01','15-01-2014-1','Dr01','Em03',1,'Customer unavailable'),
('AD02','15-01-2014-2','Dr02','Em03',2,'Completed')
go
select * from AllocationDispatch
go


---------------------------------------------------------------
-----   CREATE PROCEDURE ----------------

--- create proc to check login with data in table Manager
create Proc Check_LoginManager
@username varchar(50),
@pass varchar(32)
as
select * from Manager where ManagerAccount=@username and ManagerPassword=@pass
go
--create proc change password ----
create proc sp_ChangePasswordManager
@account varchar(50),
@pass varchar(32)
as
update Manager set ManagerPassword=@pass where ManagerAccount=@account
go


---------------------- CREATE PROC FOR EMPLOYEE ---------------------------------------------------
--- create proc to check login with data in table Employee----------------------------
create Proc Check_LoginEmployee
@username varchar(50),
@pass varchar(32)
as
select * from Employee where EmployeeAccount=@username and EmployeePassword=@pass and IsDeleted=0
go
--- create procedure get All Employee ----------------------------------------------
create proc sp_getAllEmployee
as
select * from Employee where IsDeleted=0
go
--create procedure get Employee by PermissionID --------------------------------
create proc sp_getEmployeeByPermission
@permissionID varchar(10)
as
select * from Employee where PermissionID=@permissionID and IsDeleted=0
go
--create procedure get Employee by TimeworkID ---------------------------------
create proc sp_getEmployeeByTimework
@timeworkID varchar(10)
as
select * from Employee where TimeworkID=@timeworkID and IsDeleted=0
go

-- Create procedure insert Employee --------------------------
create proc sp_insertEmployee
		@EmployeeID varchar(10),
		@EmployeeName varchar(50),
		@EmployeeBirthDay date ,
		@EmployeeGender varchar(6),
		@EmployeeAddress varchar(100),
		@EmployeePhone varchar(15),
		@PermissionID varchar(10),
		@TimeworkID varchar(10),
		@EmployeeAccount varchar(50),
		@EmployeePassword varchar(32)
as
insert into Employee(EmployeeID,EmployeeName,EmployeeBirthDay,EmployeeGender,EmployeeAddress,EmployeePhone,PermissionID,TimeworkID,EmployeeAccount,EmployeePassword) values
(@EmployeeID,@EmployeeName,@EmployeeBirthDay,@EmployeeGender,@EmployeeAddress,@EmployeePhone,@PermissionID,@TimeworkID,@EmployeeAccount,@EmployeePassword)
go
--create proc to get Employee by EmployeeAccount
create proc sp_getEmployeeByAccount
@account varchar(50)
as
select * from Employee where EmployeeAccount=@account and IsDeleted=0
go
--Create proc to get Employee by EmmployeeName-------------
create proc sp_getEmployeeByName
@name varchar(50)
as
select * from Employee where EmployeeName like '%'+@name+'%'and IsDeleted=0
go
--Create proc to get Employee by EmmployeeID-------------
create proc sp_getEmployeeByID
@id varchar(10)
as
select * from Employee where EmployeeID like '%'+@id+'%' and IsDeleted=0
go
--Create proc to update Employee Info -----
create proc sp_updateEmployee
		@EmployeeID varchar(10),
		@EmployeeName varchar(50),
		@EmployeeBirthDay date ,
		@EmployeeGender varchar(6),
		@EmployeeAddress varchar(100),
		@EmployeePhone varchar(15),
		@PermissionID varchar(10),
		@TimeworkID varchar(10)
as
update Employee 
set EmployeeName=@EmployeeName,EmployeeBirthDay=@EmployeeBirthDay,EmployeeGender=@EmployeeGender,EmployeeAddress=@EmployeeAddress,EmployeePhone=@EmployeePhone,PermissionID=@PermissionID,TimeworkID=@TimeworkID
where EmployeeID=@EmployeeID
go
---create proc delete Employee (update IsDeleted field) ------
create proc sp_deleteEmployee
@employeeID varchar(10)
as
update Employee set IsDeleted=1 where EmployeeID=@employeeID
go
---create proc get  newest Employee ---
create proc sp_getNewestEmployee
as
select Top 1 * from Employee 
order by EmployeeID desc
go
--create proc change password ----
create proc sp_ChangePassword
@account varchar(50),
@pass varchar(32)
as
update Employee set EmployeePassword=@pass where EmployeeAccount=@account
go




-------------------------- CREATE PROC FOR TIMEWORK --------------------------------------------
--- Create procedure get Timework by TimeworkID ----
create proc sp_getTimeworkByID
@timeworkID varchar(10)
as
select * from Timework where TimeworkID=@timeworkID and IsDeleted=0
go
--- Create procedure get Timework by Description ----
create proc sp_getTimeworkByDescription
@timeworkDes varchar(100)
as
select * from Timework where Description=@timeworkDes and IsDeleted=0
go
---Create procedure get allTimework ----
create proc sp_getAllTimework
as
select * from Timework where IsDeleted=0
go


-------------------------- CREATE PROC FOR PERMISSION --------------------------------------------
--- Create procedure get Permission by PermissionID ----
create proc sp_getPermissionByID
@permissionID varchar(10)
as
select * from Permission where PermissionID=@permissionID and IsDeleted=0
go
--- Create procedure get Permission by Description ----
create proc sp_getPermissionByDescription
@permissionDes varchar(100)
as
select * from Permission where Description=@permissionDes and IsDeleted=0
go

---Create procedure get allPermission ----
create proc sp_getAllPermission
as
select * from Permission where IsDeleted=0
go
--Create proc update permission ---
create proc sp_updatePermission
@id varchar(10),
@des varchar(100)
as
update Permission set Description=@des where PermissionID=@id
go
--- create proc delete permission ---
create proc sp_deletePermission
@perID varchar(10)
as
update Permission set IsDeleted=1 where PermissionID=@perID
go
---create proc get  newest Permission ---
create proc sp_getNewestPermission
as
select Top 1 * from Permission 
order by PermissionID desc
go
-- create proc add permission ---
create proc sp_addPermission
@id varchar(10),
@des varchar(100)
as
insert into Permission(PermissionID,Description) values (@id,@des)
go




------------------------- CREATE PROC FOR VENDOR --------------------------------------------
---Create procedure get allVendor ----
create proc sp_getAllVendor
as
select * from Vendor where IsDeleted=0
go
-- Create procedure insert Vendor --------------------------
create proc sp_insertVendor
		@VendorID varchar(10),
		@VendorName varchar(100),
		@VendorPhone varchar(15) ,
		@VendorAddress varchar(100),
		@VendorEmail varchar(100)
		
as
insert into Vendor(VendorID,VendorName,VendorPhone,VendorAddress,VendorEmail) values
(@VendorID,@VendorName,@VendorPhone,@VendorAddress,@VendorEmail)
go
--Create proc to update Vendor Info -----
create proc sp_updateVendor
		@VendorID varchar(10),
		@VendorName varchar(100),
		@VendorPhone varchar(15) ,
		@VendorAddress varchar(100),
		@VendorEmail varchar(100)
as
update Vendor 
set VendorName=@VendorName,VendorPhone=@VendorPhone,VendorAddress=@VendorAddress,VendorEmail=@VendorEmail
where VendorID=@VendorID
go
---create proc delete Vendor (update IsDeleted field) ------
create proc sp_deleteVendor
@Vendor varchar(10)
as
update Vendor set IsDeleted=1 where VendorID=@Vendor
go
--Create proc to get Vendor by VendorName-------------
create proc sp_getVendorName
@name varchar(50)
as
select * from Vendor where VendorName like '%'+@name+'%'and IsDeleted=0
go
--Create proc to get Vendor by VendorID-------------
create proc sp_getVendorID
@id varchar(10)
as
select * from Vendor where VendorID like '%'+@id+'%' and IsDeleted=0
go
--Create proc to get Vendor by VendorPhone-------------
create proc sp_getVendorByPhone
@VendorPhone varchar(10)
as
select * from Vendor where VendorPhone like '%'+@VendorPhone+'%' and IsDeleted=0
go
--Create proc to get Vendor by VendorAddress-------------
create proc sp_getVendorByAddress
@VendorAdd varchar(10)
as
select * from Vendor where VendorAddress like '%'+@VendorAdd+'%' and IsDeleted=0
go
---create proc get  newest Vendor---
create proc sp_getNewestVendor
as
select Top 1 * from Vendor
order by VendorID desc

go
--- Create procedure get Vendor by Check Name ----
create proc sp_getVendorByName
@VendorName varchar(100)
as
select * from Vendor where VendorName=@VendorName and IsDeleted=0
go
------------------------- CREATE PROC FOR CATEGORY --------------------------------------------
---Create procedure get allCategory ----
create proc sp_getAllCategory
as
select * from Category where IsDeleted=0
go

--create proc get Category by ID ---
create proc sp_getCategoryByID
@id varchar(10)
as
select * from Category where CategoryID like '%'+@id+'%' and IsDeleted=0
go
--create proc get Category by Name ---
create proc sp_getCategoryByName
@id varchar(50)
as
select * from Category where CategoryName like '%'+@id+'%' and IsDeleted=0
go



---------------------------- CREATE PROC FOR PRODUCT --------------------------------------------------------
--create proc get all product----
create proc sp_getAllProduct
as
select * from Product where IsDeleted=0
go

-- create proc get product by productID ------
create proc sp_getProductByID
@id varchar(10)
as
select * from Product where ProductID like '%'+@id+'%' and IsDeleted=0
go
--create proc get product by productName ----
create proc sp_getProductByName
@name varchar(50)
as
select * from Product where ProductName like '%'+@name+'%' and IsDeleted=0
go
--create proc get product by category -----
create proc sp_getProductByCategory
@category varchar(10)
as
select * from Product where Product.CategoryID like '%'+@category+'%' and IsDeleted=0
go
--create proc update product --
create proc sp_updateProduct
@ProductID varchar(10) ,
	@ProductName varchar(50),
		@ProductUnits varchar(20) ,
		@ProductPrice float ,
		@ProductQuantities int ,
		@CategoryID varchar(10) ,
		@VendorID varchar(10),
		@Description varchar(100)
as
update Product set ProductName=@ProductName,ProductUnits=@ProductUnits,ProductPrice=@ProductPrice,ProductQuantities=@ProductQuantities,
					CategoryID=@CategoryID,VendorID=@VendorID,Description=@Description
where ProductID=@ProductID
go			
					
--create proc add new product --
create proc sp_addProduct
@ProductID varchar(10) ,
	@ProductName varchar(50),
		@ProductUnits varchar(20) ,
		@ProductPrice float ,
		@ProductQuantities int ,
		@CategoryID varchar(10) ,
		@VendorID varchar(10),
		@Description varchar(100)
as
insert into Product(ProductID,ProductName,ProductUnits,ProductPrice,ProductQuantities,CategoryID,VendorID,Description)
values(@ProductID,@ProductName,@ProductUnits,@ProductPrice,@ProductQuantities,@CategoryID,@VendorID,@Description) 
go
--create proc get newest product ---
create proc sp_getNewestProduct
as
select Top 1 * from Product 
order by ProductID desc
go
--create proc check the existence of product name ---
create proc sp_checkProductName
@productName varchar(50)
as
select *from Product where ProductName=@productName and IsDeleted=0
go

--- Create procedure get Product by Vendor ----
create proc sp_getProductByVendor
@vendorID varchar(10)
as
select * from Product where VendorID=@vendorID and IsDeleted=0
go
--- create proc delete product---
create proc sp_deleteProduct
@product varchar(10)
as
update Product set IsDeleted=1 where ProductID=@product
go

-- create proc check constraint of product with sale orders--
create proc sp_checkConstraintProductWithSaleOder
@product varchar(10)
as
select * from SaleOrders inner join SaleOrdersDetail on SaleOrders.SaleOrdersID=SaleOrdersDetail.SaleOrdersID
where ProductID=@product and (SaleOrders.StockIssue=0 or Payment=0)
go 

--create proc check constraint of product with Purchase Order---
create proc sp_checkConstraintProductWithPurchaseOrder
@product varchar(10)
as
select * from PurchaseOrders inner join PurchaseOrdersDetail on PurchaseOrders.PurchaseOrdersID=PurchaseOrdersDetail.PurchaseOrdersID
where ProductID=@product and (PurchaseOrders.Status=0 or PurchaseOrders.Approve=0)
go


